Computer systems generally include one or more processors interfaced to a temporary data storage device such as a memory device and one or more persistent data storage devices such as disk drives. Data is usually transferred between the memory device and the disk drives over a communications bus or similar. Once data has been transferred from a disk drive to a memory device accessible by a processor, database software is then able to examine the data to determine if it satisfies the conditions of a query.
Queries issued to the database system may be processed with a multitude of possible execution plans. Some execution plans are more cost efficient than other execution plans based on several factors including the number and size of intermediate result sets required to be constructed. Some queries are able to undergo query optimization that can enable dramatic improvements in performance in such database systems. A cost based query optimizer evaluates some or all possible execution plans for a query and estimates the cost of each plan based on resource utilization. The optimizer eliminates costly plans and chooses a relatively low cost plan.
Many relational database systems have the capability of persistently storing a compiled execution plan for a SQL query and reusing the saved plan for subsequent executions of the same query. This technique saves the CPU cost of having to repeatedly parse and optimize frequently executed queries. However, one of the drawbacks of using the same stored plan over a long period of time is that it may become sub-optimal or stale. More specifically, using the same stored plan over a long period of time prevents the query optimizer from potentially finding a new and more efficient plan, especially in those cases where there has been a significant change in data demographics since the plan was last saved.
Many database systems offer a mechanism for a user or database administrator to manually force a recompile of one or more stored plans if it is felt that a new and better plan will likely result. Thus human element of control often leads to difficulties particularly where a typical database system has hundreds or thousands of frequently executed queries. Any method that relies on manual intervention is often not practical.